package com.cn.parks.dao.impl;

import com.cn.parks.dao.UserCarDao;
import com.cn.parks.entity.UserCar;
import com.cn.parks.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户车辆DAO实现类
 */
public class UserCarDaoImpl implements UserCarDao {

    /**
     * 添加用户车辆信息
     */
    @Override
    public int insert(UserCar userCar) throws Exception {
        String sql = "INSERT INTO user_car (user_id, car_number, car_brand, car_model, car_color,seat_num, car_type, status) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userCar.getUserId());
            pstmt.setString(2, userCar.getCarNumber());
            pstmt.setString(3, userCar.getCarBrand());
            pstmt.setString(4, userCar.getCarModel());
            pstmt.setString(5, userCar.getCarColor());
            pstmt.setString(6, userCar.getSeatNum());
            pstmt.setInt(7, userCar.getCarType());
            pstmt.setInt(8, userCar.getStatus());
            return pstmt.executeUpdate();
        }
    }

    /**
     * 删除用户车辆信息
     * @param carId
     * @return
     * @throws Exception
     */
    @Override
    public int deleteById(Integer carId) throws Exception {
        String sql = "DELETE FROM user_car WHERE car_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, carId);
            return pstmt.executeUpdate();
        }
    }
    /**
     * 更新用户车辆信息
     */
    @Override
    public int update(UserCar userCar) throws Exception {
        String sql = "UPDATE user_car SET car_number=?, car_brand=?, car_model=?, car_color=?, car_type=?, status=? ,seat_num = ? " +
                    "WHERE car_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, userCar.getCarNumber());
            pstmt.setString(2, userCar.getCarBrand());
            pstmt.setString(3, userCar.getCarModel());
            pstmt.setString(4, userCar.getCarColor());
            pstmt.setInt(5, userCar.getCarType());
            pstmt.setInt(6, userCar.getStatus());
            pstmt.setString(7, userCar.getSeatNum());
            pstmt.setInt(8, userCar.getCarId());
            
            return pstmt.executeUpdate();
        }
    }
    /**
     * 根据ID获取用户车辆信息
     */
    @Override
    public UserCar getById(Integer carId) throws Exception {
        String sql = "SELECT * FROM user_car WHERE car_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, carId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return extractUserCar(rs);
                }
            }
        }
        return null;
    }

    /**
     * 更新用户车辆信息
     */
    @Override
    public int updateSeatNum(Integer carId, String seatNum) throws Exception {
        String sql = "UPDATE user_car SET seat_num = ? WHERE car_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, seatNum);
            pstmt.setInt(2, carId);
            return pstmt.executeUpdate();
        }
    }

    /**
     * 获取所有用户车辆信息
     */
    @Override
    public List<UserCar> getAll() throws Exception {
        String sql = "SELECT * FROM user_car ORDER BY create_time DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            List<UserCar> list = new ArrayList<>();
            while (rs.next()) {
                list.add(extractUserCar(rs));
            }
            return list;
        }
    }

    /**
     * 根据用户ID获取用户车辆信息
     * @param userId
     * @return
     * @throws Exception
     */
    @Override
    public List<UserCar> getByUserId(Integer userId) throws Exception {
        String sql = "SELECT * FROM user_car WHERE user_id = ? ORDER BY create_time DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, userId);
            try (ResultSet rs = pstmt.executeQuery()) {
                List<UserCar> list = new ArrayList<>();
                while (rs.next()) {
                    list.add(extractUserCar(rs));
                }
                return list;
            }
        }
    }
    /**
     * 根据车牌号获取用户车辆信息
     * @param carNumber
     * @return
     * @throws Exception
     */
    @Override
    public List<UserCar> getByCarNumber(String carNumber) throws Exception {
        String sql = "SELECT * FROM user_car WHERE car_number LIKE ? ORDER BY create_time DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + carNumber + "%");
            try (ResultSet rs = pstmt.executeQuery()) {
                List<UserCar> list = new ArrayList<>();
                while (rs.next()) {
                    list.add(extractUserCar(rs));
                }
                return list;
            }
        }
    }
    /**
     * 根据车位编号查询车辆是否存在
     */

    @Override
public UserCar getByCarNum(String carNumber) throws Exception {
    String sql = "SELECT * FROM user_car WHERE car_number = ?";
    try (Connection conn = DBUtil.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, carNumber);
        try (ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return extractUserCar(rs);
            }
            return null; // 如果没有找到，返回 null
        }
    }
}

    /**
     * 根据车牌号获取用户车辆信息
     * @return
     * @throws Exception
     */
    @Override
    public int updateStatus(Integer carId, Integer status) throws Exception {
        String sql = "UPDATE user_car SET status = ? WHERE car_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, status);
            pstmt.setInt(2, carId);
            return pstmt.executeUpdate();
        }
    }

    /**
     * 获取固定车位总数
     */
    @Override
    public int fixedCountAll() throws Exception {
        String sql = "SELECT COUNT(*) FROM user_car WHERE car_type = 1";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    /**
     * 获取VIP车位总数
     * @return
     * @throws Exception
     */
    @Override
    public int vipCountAll() throws Exception {
        String sql = "SELECT COUNT(*) FROM user_car WHERE car_type = 2";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    /**
     * 获取所有固定和VIP车辆信息
     */
    @Override
    public List<UserCar> getAllCar() throws Exception {
        String sql = "SELECT * FROM user_car WHERE car_type = 1 or car_type = 2";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            List<UserCar> list = new ArrayList<>();
            while (rs.next()) {
                list.add(extractUserCar(rs));
            }
            return list;
        }
    }

    /**
     * 获取今日新增车辆总数
     */
    @Override
    public int todayCount() throws Exception {
        String sql = "SELECT COUNT(*) FROM user_car WHERE DATE(create_time) = CURDATE()";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    /**
     * 根据车牌号获取车辆id
     * @return
     * @throws Exception
     */
    @Override
    public Integer getByCarId(String carNumber) throws Exception {
        String sql = "SELECT car_id FROM user_car WHERE car_number = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, carNumber);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("car_id");
                }
            }
        }
        return 0;
    }
    /**
     * ResultSet中提取UserCar对象
     */
    private UserCar extractUserCar(ResultSet rs) throws SQLException {
        UserCar car = new UserCar();
        car.setCarId(rs.getInt("car_id"));
        car.setUserId(rs.getInt("user_id"));
        car.setCarNumber(rs.getString("car_number"));
        car.setCarBrand(rs.getString("car_brand"));
        car.setCarModel(rs.getString("car_model"));
        car.setCarColor(rs.getString("car_color"));
        car.setSeatNum(rs.getString("seat_num"));
        car.setCarType(rs.getInt("car_type"));
        car.setStatus(rs.getInt("status"));
        return car;
    }
} 